<?php
include_once("../../../Dao/db.php");

$nowPageNum = $_GET["nowPageNum"];
$RECORD_NUMBER_PER_PAGE = 20;
$fromRow = ($nowPageNum-1) * $RECORD_NUMBER_PER_PAGE;
// 図の種類を判断する
$graphType = $_GET["graphType"];
// 折れ線の場合
if($graphType == "0")
{
	include("../../CommonClass/GraphMakerLine.php");
	session_start();
	$myGraph = new LineMaker;
	$arrayY1 = array();
	$arrayY2 = array();
	$arrayX = array();
	$sql = getSelectSql();
	$sql = $sql.getFilterCol($_GET["graphCol1"]).",".getFilterCol($_GET["graphCol2"])." ";
	$sql = $sql.getFromSql();
	$sql = $sql."where listingmst.id = campaign.listingType ";
	$sql = $sql.getWhereSql();
	$sql = $sql.getGroupSql();
	$sql = $sql.getHavingSql();
	$sql = $sql.getOrderBySql();
	$sql = $sql."limit ".$fromRow.",".$RECORD_NUMBER_PER_PAGE;	
	$result = mysql_query($sql);
	$rows = mysql_num_rows($result);
	if($rows > 0)
	{
		for ($i=0;$i<$rows;$i++)
		{
			$data = mysql_fetch_row($result);
			// 表示形式はキャンペーン別の場合
			if($_GET["reportAxis"] == "id")
			{
				if($data[2] == null)
				{
					array_push($arrayY1,0);
				}
				else
				{
					array_push($arrayY1,$data[2]);
				}
				if($data[3] == null)
				{
					array_push($arrayY2,0);
				}
				else
				{
					array_push($arrayY2,$data[3]);
				}
				array_push($arrayX,$data[0]);
			}
			// 表示形式が曜日別の場合
			else if($_GET["reportAxis"] == "dow")
			{
				if($data[1] == null)
				{
					array_push($arrayY1,0);
				}
				else
				{
					array_push($arrayY1,$data[1]);
				}
				if($data[2] == null)
				{
					array_push($arrayY2,0);
				}
				else
				{
					array_push($arrayY2,$data[2]);
				}
				array_push($arrayX,getDayOfTheWeekName($data[0]));				
			}
			else
			{
				if($data[1] == null)
				{
					array_push($arrayY1,0);
				}
				else
				{
					array_push($arrayY1,$data[1]);
				}
				if($data[2] == null)
				{
					array_push($arrayY2,0);
				}
				else
				{
					array_push($arrayY2,$data[2]);
				}
				// 週別の場合
				if($_GET["reportAxis"] == "w")
				{
					array_push($arrayX,getWeekDate($data[0]));
				}
				else
				{
					array_push($arrayX,$data[0]);
				}				
			}
		}
	}
	// データがない場合
	else if ($rows == 0)
	{
		$arrayY1 = array(0);
		$arrayY2 = array(0);
		$arrayX = array(0);
	}
	// line
	$myGraph->setL1DataY($arrayY1);
	// bar
	$myGraph->setL2DataY($arrayY2);
	$myGraph->setDataX($arrayX);
	// データがない場合
	if($rows == 0)
	{
		$myGraph->setXtitle("データがない");
		$myGraph->setYtitle("データがない");
		$myGraph->setY2ScaleMax(10);				
	}
	else
	{
		$myGraph->setYtitle($_GET["graphCol1"]);
		$myGraph->setXtitle($_GET["graphCol2"]);
		$myGraph->setY2ScaleMax(max(max($arrayY1),max($arrayY2)));
		if(max($arrayY1) == 0 && max($arrayY2) == 0)
		{
			$myGraph->setY2ScaleMax(1);
		}	
	}

	// 図サイズをセットする
	$myGraph->setGraphSize($_GET["graphSize"]);
	//$myGraph->setGraphSize(2);
	$myGraph->setLineLegend($_GET["graphCol1"]);
	$myGraph->setBarLegend($_GET["graphCol2"]);
	$myGraph->graphMake();
}
// 分布図の場合
else
{
	include("../../CommonClass/GraphMakerScatter.php");
	session_start();
	$myGraph = new ScatterMaker;
	$arrayX = array();
	$arrayY = array();
	$sql = getSelectSql();
	$sql = $sql.getFilterCol($_GET["graphCol1"]).",".getFilterCol($_GET["graphCol2"])." ";
	$sql = $sql.getFromSql();
	$sql = $sql."where listingmst.id = campaign.listingType ";
	$sql = $sql.getWhereSql();
	$sql = $sql.getGroupSql();
	$sql = $sql.getHavingSql();
	$sql = $sql.getOrderBySql();
	$sql = $sql."limit ".$fromRow.",".$RECORD_NUMBER_PER_PAGE;		
	$result = mysql_query($sql);
	$rows = mysql_num_rows($result);
	if($rows > 0)
	{
		for ($i=0;$i<$rows;$i++)
		{
			$data = mysql_fetch_row($result);
			// 表示形式はキャンペーン別の場合
			if($_GET["reportAxis"] == "id")
			{
				if($data[2] == null)
				{
					array_push($arrayX,0);
				}
				else
				{
					array_push($arrayX,$data[2]);
				}
				if($data[3] == null)
				{
					array_push($arrayY,0);
				}
				else
				{
					array_push($arrayY,$data[3]);
				}
			}
			else
			{
				if($data[1] == null)
				{
					array_push($arrayX,0);
				}
				else
				{
					array_push($arrayX,$data[1]);
				}
				if($data[2] == null)
				{
					array_push($arrayY,0);
				}
				else
				{
					array_push($arrayY,$data[2]);
				}
			}
		}
	}
	else if($rows == 0)
	{
		$arrayX = array(0);
		$arrayY = array(0);
	}		
	
	$myGraph->setDataX($arrayX);
	$myGraph->setDataY($arrayY);
	
	// 図サイズをセットする
	$myGraph->setGraphSize($_GET["graphSize"]);

	if($rows == 0)
	{
		$myGraph->setXtitle("データがない");
		$myGraph->setYtitle("データがない");
	}
	else
	{
		$myGraph->setXtitle($_GET["graphCol2"]);
		$myGraph->setYtitle($_GET["graphCol1"]);
	}
	$myGraph->graphMake();
}

function getSelectSql()
{
	// 表示形式を判断する
	// キャンペーン別の場合
	if($_GET["reportAxis"] == "id")
	{
		$sql = "select campaign.name name,campaign.camp_ID campaignId,";
	}
	// 日別
	else if ($_GET["reportAxis"] == "d")
	{
		$sql = "select DATE_FORMAT(adcvreport.date,'%Y%m%d') date,";
	}
	// 週別
	else if ($_GET["reportAxis"] == "w")
	{
		$sql = "select weekly,";
	}
	// 曜日別
	else if ($_GET["reportAxis"] == "dow")
	{
		$sql = "select dayOfTheWeek,";
	}
	// 月別
	else if ($_GET["reportAxis"] == "m")
	{
		$sql = "select DATE_FORMAT(adcvreport.date,'%Y-%m') month,";
	}
	
	return $sql;
}

function getFromSql()
{
	// キャンペーン別の場合
	if($_GET["reportAxis"] == "id")
	{
		$sql = $sql."from listingmst,(((campaign left join adcvreport on campaign.camp_ID = adcvreport.campaign_id) ";
		$sql = $sql."left join adreport ";
		$sql = $sql."on adcvreport.date = adreport.date ";
		$sql = $sql."and adcvreport.memberid = adreport.memberId ";	
		$sql = $sql."and adcvreport.listingtype = adreport.listingType ";
		$sql = $sql."and adcvreport.campaign_id = adreport.campaign_Id ";
		$sql = $sql."and adcvreport.adGroup_id = adreport.adGroup_Id) ";
		$sql = $sql."inner join member on member.memberId = adreport.memberId ";
		$sql = $sql."and member.status = 1 ";
		$sql = $sql."and member.joinRanking = 1) ";				
	}
	else
	{
		$sql = $sql."from listingmst,(((campaign left join adcvreport on campaign.camp_ID = adcvreport.campaign_id) ";
		$sql = $sql."left join adreport ";
		$sql = $sql."on adcvreport.date = adreport.date ";
		$sql = $sql."and adcvreport.memberid = adreport.memberId ";	
		$sql = $sql."and adcvreport.listingtype = adreport.listingType ";
		$sql = $sql."and adcvreport.campaign_id = adreport.campaign_Id ";
		$sql = $sql."and adcvreport.adGroup_id = adreport.adGroup_Id) ";
		$sql = $sql."inner join member on member.memberId = adreport.memberId ";
		$sql = $sql."and member.status = 1 ";
		$sql = $sql."and member.joinRanking = 1) ";											
	}
	return $sql;
}

function getWhereSql()
{
	// 詳細検索条件のフラグを判断する
	// 詳細検索がない場合
	if($_GET["searchDetail"] == "display:none")
	{
		$sql = "and TO_DAYS('".$_GET["fromDate"]."')<=TO_DAYS(adcvreport.date) and TO_DAYS(adcvreport.date)<=TO_DAYS('".$_GET["toDate"]."') ";			
	}
	// 詳細検索がある場合
	else
	{
		$sql = "and TO_DAYS('".$_GET["fromDate"]."')<=TO_DAYS(adcvreport.date) and TO_DAYS(adcvreport.date)<=TO_DAYS('".$_GET["toDate"]."') ";					
		// キャンペーンIDの場合
		if($_GET["searchCol"] == "id")
		{
			if(trim($_GET["searchText"]) != null && trim($_GET["searchText"]) != "")
			{
				$sql = $sql."and campaign.camp_ID".getLikeSql(trim($_GET["searchText"]),$_GET["searchType"])." ";
			}
		}
		// キャンペーン名の場合
		else if($_GET["searchCol"] == "name")
		{
			if(trim($_GET["searchText"]) != null && trim($_GET["searchText"]) != "")
			{
				$sql = $sql."and campaign.name".getLikeSql(trim($_GET["searchText"]),$_GET["searchType"])." ";
			}
		}
		// 媒体の条件を追加する
		// 指定の場合
		if($_GET["searchMediaSelect"] != "0")
		{
			$sql = $sql."and campaign.listingType= ".$_GET["searchMediaSelect"]." ";
		}
		// 同期状態の条件を追加する
		// 更新待ちの場合
		if($_GET["searchSync"] == "2")
		{
			$sql = $sql."and campaign.sync=2 ";
		}
		// 同期済の場合
		else if($_GET["searchSync"] == "1")
		{
			$sql = $sql."and campaign.sync=1 ";			
		}
		// エラーの場合
		else if($_GET["searchSync"] == "3")
		{
			$sql = $sql."and campaign.sync=3 ";			
		}
	}
	// 削除済みも表示の条件を追加する
	// 削除非表示
	if($_GET["showDeleted"] != "1")
	{
		$sql = $sql."and campaign.status <> 9 ";
	}
	// ユーザーが指定の場合
	if($_GET["searchUserId"] != "0")
	{
		$sql = $sql."and member.memberId=".$_GET["searchUserId"]." ";
	}
	
	$sql = $sql."and adcvreport.cvId =-1 ";

	return $sql;
}

function getFilterCol($filterCol)
{
	// %の場合対応
	if($filterCol == "ctr")
	{
		$filterColSql = "100*sum(adreport.clickCnt)/sum(adreport.impression) ctr";
	}
	else if($filterCol == "cvr")
	{
		$filterColSql = "100*sum(adcvreport.cv)/sum(adreport.clickCnt) cvr";
	}
	else if($filterCol == "cpa")
	{
		$filterColSql="sum(adreport.cost)/sum(adcvreport.cv) cpa";
	}
	else if($filterCol == "roas")
	{
		$filterColSql="sum(adcvreport.sales)/sum(adreport.cost) roas";
	}
	else if($filterCol == "cpm")
	{
		$filterColSql = "1000*sum(adreport.cost)/sum(adreport.impression) cpm";
	}
	else if($filterCol == "cpc")
	{
		$filterColSql = "sum(adreport.cost)/sum(adreport.clickCnt) cpc";
	}
	else if($filterCol == "salesValue")
	{
		$filterColSql = "sum(adcvreport.sales)/sum(adcvreport.cv) salesValue";
	}
	// sumの場合
	else
	{
		// Click-CV間隔の場合
		if($_GET["graphCol1"] == "clickCvSpan")
		{
			$filterColSql = "avg(".$filterCol.") ";
		}
		else
		{
			$filterColSql = "sum(".$filterCol.") ";
		}			
	}
	
	return 	$filterColSql;
}

function getDayOfTheWeekName($dayOfTheWeekId)
{
	if($dayOfTheWeekId == "0")
	{
		return "日曜日";
	}
	if($dayOfTheWeekId == "1")
	{
		return "月曜日";
	}
	if($dayOfTheWeekId == "2")
	{
		return "火曜日";
	}
	if($dayOfTheWeekId == "3")
	{
		return "水曜日";
	}
	if($dayOfTheWeekId == "4")
	{
		return "木曜日";
	}
	if($dayOfTheWeekId == "5")
	{
		return "金曜日";
	}
	if($dayOfTheWeekId == "6")
	{
		return "土曜日";
	}
}

function getWeekDate($dateTime)
{
	$weekdate = date("Y-m-d",strtotime($dateTime));
	return $weekdate."~".strval(date("Y-m-d",strtotime($dateTime) + 24*60*60*(6))); 		
}

function getLikeSql($text,$type)
{
	// 全部一致の場合
	if($type == "0")
	{
		return " = '".$text."' ";
	}
	// 部分一致の場合
	if($type == "1")
	{
		return " like binary '%$text%' ";
	}
}

function getGroupSql()
{
	// キャンペーン別の場合
	if($_GET["reportAxis"] == "id")
	{
		$sql = "group by campaign.name,campaign.camp_ID ";
	}
	// 日別
	else if ($_GET["reportAxis"] == "d")
	{
		$sql = "group by date ";
	}
	// 週別
	else if ($_GET["reportAxis"] == "w")
	{
		$sql = "group by weekly ";
	}
	// 曜日別
	else if ($_GET["reportAxis"] == "dow")
	{
		$sql = "group by dayOfTheWeek ";
	}
	// 月別
	else if ($_GET["reportAxis"] == "m")
	{
		$sql = "group by month ";
	}
		
	return $sql;
}

function getOrderBySql()
{
	$sql = "";
	// キャンペーン別
	if($_GET["reportAxis"] == "id")
	{
		$sql = "order by name ";
	}
	// 日別
	if ($_GET["reportAxis"] == "d")
	{
		$sql = "order by date ";
	}
	// 週別
	else if ($_GET["reportAxis"] == "w")
	{
		$sql = "order by weekly ";
	}
	// 曜日別
	else if ($_GET["reportAxis"] == "dow")
	{
		$sql = "order by dayOfTheWeek ";
	}
	// 月別
	else if ($_GET["reportAxis"] == "m")
	{
		$sql = "order by month ";
	}
		
	return $sql;
}


function getHavingFilter($filterCol)
{
	// %の場合対応
	if($filterCol == "ctr")
	{
		$filterColSql = "100*sum(adreport.clickCnt)/sum(adreport.impression)";
	}
	else if($filterCol == "cvr")
	{
		$filterColSql = "100*sum(adcvreport.cv)/sum(adreport.clickCnt)";
	}
	else if($filterCol == "cpa")
	{
		$filterColSql="sum(adreport.cost)/sum(adcvreport.cv)";
	}
	else if($filterCol == "roas")
	{
		$filterColSql="sum(adcvreport.sales)/sum(adreport.cost)";
	}
	else if($filterCol == "cpm")
	{
		$filterColSql = "1000*sum(adreport.cost)/sum(adreport.impression)";
	}
	else if($filterCol == "cpc")
	{
		$filterColSql = "sum(adreport.cost)/sum(adreport.clickCnt)";
	}
	else if($filterCol == "salesValue")
	{
		$filterColSql = "sum(adcvreport.sales)/sum(adcvreport.cv)";
	}
	else if($filterCol == "salesValue")
	{
		$filterColSql = "sum(adcvreport.sales)/sum(adcvreport.cv)";
	}
	// sumの場合
	else
	{
		// Click-CV間隔の場合
		if($_GET["graphCol1"] == "clickCvSpan")
		{
			$filterColSql = "avg(".$filterCol.") ";
		}
		else
		{
			$filterColSql = "sum(".$filterCol.") ";
		}			
	}
	
	return 	$filterColSql;
}

function getHavingSql()
{
	// 詳細検索がない場合
	if($_GET["searchDetail"] == "display:none")
	{
		
	}
	else
	{
		// filter_colとfilter3_colがあるの場合
		if(trim($_GET["filterValue"]) != null && trim($_GET["filterValue"]) != "" &&
		   trim($_GET["filter3Value"]) != null && trim($_GET["filter3Value"]) != "")
		{
			// filterTypeが以上の場合
			if($_GET["filterType"] == "1")
			{
				$sql = "having (".getHavingFilter($_GET["filterCol"]).">=".trim($_GET["filterValue"]).") ";
			}
			// filterTypeが以下の場合
			else
			{
				$sql = "having (".getHavingFilter($_GET["filterCol"])."<=".trim($_GET["filterValue"]).") ";
			}			
			// filter3Typeが以上の場合
			if($_GET["filter3Type"] == "1")
			{
				$sql = $sql."and (".getHavingFilter($_GET["filter3Col"]).">=".trim($_GET["filter3Value"]).") ";
			}
			// filter3Typeが以下の場合
			else
			{
				$sql = $sql."and (".getHavingFilter($_GET["filter3Col"])."<=".trim($_GET["filter3Value"]).") ";
			}
			return $sql;						
		}
		// filter_colとfilter3_colが全部入力しません
		else if((trim($_GET["filterValue"]) == null || trim($_GET["filterValue"]) == "") &&
		   (trim($_GET["filter3Value"]) == null || trim($_GET["filter3Value"]) == ""))
		{
			return " ";
		}
		else
		{
			// filter_colの条件を追加する
			if(trim($_GET["filterValue"]) != null && trim($_GET["filterValue"]) != "")
			{
				// filterTypeが以上の場合
				if($_GET["filterType"] == "1")
				{
					$sql = "having (".getHavingFilter($_GET["filterCol"]).">=".trim($_GET["filterValue"]).") ";
				}
				// filterTypeが以下の場合
				else
				{
					$sql = "having (".getHavingFilter($_GET["filterCol"])."<=".trim($_GET["filterValue"]).") ";
				}
				return $sql;							
			}
			// filter3_colの条件を追加する
			else
			{
				// filter3_colの条件を追加する
				if(trim($_GET["filter3Value"]) != null && trim($_GET["filter3Value"]) != "")
				{
					// filter3Typeが以上の場合
					if($_GET["filter3Type"] == "1")
					{
						$sql = "having (".getHavingFilter($_GET["filter3Col"]).">=".trim($_GET["filter3Value"]).") ";
					}
					// filter3Typeが以下の場合
					else
					{
						$sql = "having (".getHavingFilter($_GET["filter3Col"])."<=".trim($_GET["filter3Value"]).") ";
					}
					return $sql;			
				}	
			}					
		}		
	}	
}
?>